const OpenAI = require("openai");
const XLSX = require("xlsx");
const fs = require("fs");
const path = require("path");

// 初始化 OpenAI
require("dotenv").config(); // 顶部引入 dotenv

const openai = new OpenAI({
  apiKey: process.env.OPENAI_API_KEY,
  baseURL: process.env.BASE_URL
});


// 原始数据文件
const inputFilePath = "Data.xls";
// 目标文件
const outputFilePath = "example_sentences_with_word.xlsx";

// 读取原始 Excel 文件中的单词列
function getWordsFromExcel(filePath) {
  const workbook = XLSX.readFile(filePath);
  const sheet = workbook.Sheets[workbook.SheetNames[0]];
  const data = XLSX.utils.sheet_to_json(sheet, { header: 1 });

  // 去掉表头，从第2行开始读取第1列的单词
  const words = data.slice(1).map(row => row[0]).filter(Boolean);
  return words;
}

// 创建或加载 Excel
function loadOrCreateWorkbook(filePath) {
  if (fs.existsSync(filePath)) {
    return XLSX.readFile(filePath);
  } else {
    const wb = XLSX.utils.book_new();
    const ws = XLSX.utils.aoa_to_sheet([["Word", "Sentence", "Translation"]]);
    XLSX.utils.book_append_sheet(wb, ws, "Sheet1");
    return wb;
  }
}

// 将数据写入 Excel 文件
function appendToExcel(word, sentence, translation, filePath) {
  const workbook = loadOrCreateWorkbook(filePath);
  const worksheet = workbook.Sheets[workbook.SheetNames[0]];

  const data = XLSX.utils.sheet_to_json(worksheet, { header: 1 });
  data.push([word, sentence, translation]);

  const newSheet = XLSX.utils.aoa_to_sheet(data);
  workbook.Sheets[workbook.SheetNames[0]] = newSheet;

  XLSX.writeFile(workbook, filePath);
}

// 请求 AI 获取例句
async function getSentenceFromAI(word) {
  try {
    const chatCompletion = await openai.chat.completions.create({
      model: "gpt-3.5-turbo-0125",
      messages: [{
        role: "user",
        content: `请用英文给出一个包含单词 "${word}" 的例句,例句稍微难一点，并提供中文翻译。数据返回为json格式为{"Sentence":"","Translation":""}`
      }]
    });

    const result = chatCompletion.choices[0].message.content;
    const jsonData = JSON.parse(result);
    return jsonData;
  } catch (error) {
    console.error(`❌ 处理 "${word}" 出错:`, error.message);
    return null;
  }
}

// 主函数
async function main() {
  const words = getWordsFromExcel(inputFilePath);
  console.log(`🔍 共找到 ${words.length} 个单词，将每隔 10 秒处理一个...`);

  for (let i = 4490; i < words.length; i++) {
    const word = words[i];
    console.log(`\n📌 [${i + 1}/${words.length}] 正在处理单词：${word}`);

    let result = null;
    let retryCount = 0;

    while (!result) {
      result = await getSentenceFromAI(word);
      retryCount++;

      if (!result) {
        console.log(`🔁 第 ${retryCount} 次重试 "${word}" 中...`);
        await new Promise(resolve => setTimeout(resolve, 1000)); // 每次失败后等 5 秒重试
      }
    }

    appendToExcel(word, result.Sentence, result.Translation, outputFilePath);
    console.log(`✅ 已写入：${word}`);

    if (i < words.length - 1) {
      await new Promise(resolve => setTimeout(resolve, 1000)); // 每个单词之间等待 10 秒
    }
  }

  console.log("\n🎉 所有单词处理完成！");
}


main();
